package io.renren.modules.contract.controller;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import io.renren.common.utils.BigDecimalUtils;
import io.renren.modules.contract.entity.ContractPaymentEntity;
import io.renren.modules.contract.entity.PaymentInfoEntity;
import io.renren.modules.contract.entity.TenderingBatchEntity;
import io.renren.modules.contract.service.TenderingBatchService;
import io.renren.modules.project.entity.ProjectEntityEntity;
import io.renren.modules.project.entity.ProjectPaymentEntity;
import io.renren.modules.project.service.ProjectEntityService;
import org.apache.commons.collections.map.HashedMap;
import org.apache.commons.lang.StringUtils;
//import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import io.renren.modules.contract.entity.ContractReceiptEntity;
import io.renren.modules.contract.service.ContractReceiptService;
import io.renren.common.utils.PageUtils;
import io.renren.common.utils.Query;
import io.renren.common.utils.R;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


/**
 * 收款合同表
 *
 * @author chenshun
 * @email sunlightcs@gmail.com
 * @date 2019-07-31 16:08:55
 */
@RestController
@RequestMapping("/contract/contractreceipt")
public class ContractReceiptController {
    @Autowired
    private ContractReceiptService contractReceiptService;
    @Autowired
    private ProjectEntityService projectEntityService;
    @Autowired
    private TenderingBatchService tenderingBatchService;

    private int cols = 13;//excel里表格列

    private String sheetTitle = "收付款详情";

    public void setCols(int cols) {
        this.cols = cols;
    }

    public void setSheetTitle(String sheetTitle) {
        this.sheetTitle = sheetTitle;
    }

    /**
     * 列表
     */
    @RequestMapping("/list")
    @RequiresPermissions("contract:contractreceipt:list")
    public R list(@RequestParam Map<String, Object> params) {
        //查询列表数据
        Query query = new Query(params);

        List<ContractReceiptEntity> contractReceiptList = contractReceiptService.queryList(query);
        int total = contractReceiptService.queryTotal(query);

        PageUtils pageUtil = new PageUtils(contractReceiptList, total, query.getLimit(), query.getPage());

        return R.ok().put("page", pageUtil);
    }


    /**
     * 信息
     */
    @RequestMapping("/info/{id}")
    @RequiresPermissions("contract:contractreceipt:info")
    public R info(@PathVariable("id") String id) {
        ContractReceiptEntity contractReceipt = contractReceiptService.queryObject(id);

        return R.ok().put("contractReceipt", contractReceipt);
    }

    /**
     * 保存
     */
    @RequestMapping("/save")
    public R save(@RequestBody ContractReceiptEntity contractReceipt) {
        contractReceiptService.save(contractReceipt);
        return R.ok();
    }

    /**
     * 修改
     */
    @RequestMapping("/update")
    public R update(@RequestBody ContractReceiptEntity contractReceipt) {
        contractReceiptService.update(contractReceipt);
        contractReceiptService.maintainPaymentRatio(contractReceipt);
        return R.ok();
    }

    /**
     * 超批次预警
     */
    @RequestMapping("/earlyWarning")
    public R earlyWarning(@RequestBody ContractReceiptEntity contractReceipt) {
        System.out.println(contractReceipt);
        //按照批次统计合同金额
        BigDecimal bigDecimal = contractReceiptService.sumContractTotal(contractReceipt.getTenderBatch());
        if (bigDecimal != null){
            bigDecimal.add(contractReceipt.getContractTotal());
        }else {
            bigDecimal = contractReceipt.getContractTotal();
        }
        TenderingBatchEntity tenderingBatchEntity = tenderingBatchService.queryObject(Long.valueOf(contractReceipt.getTenderBatch()));
        int flag = bigDecimal.compareTo(tenderingBatchEntity.getMoney());
        if (flag > 0){
            //合同金额超过招标批次金额
            System.out.println("合同金额超过招标批次金额");
            return R.ok("0");
        }else {
            System.out.println("合同金额小于招标批次金额");
            return R.ok("1");
        }
    }

    /**
     * 删除
     */
    @RequestMapping("/delete")
    @RequiresPermissions("contract:contractreceipt:delete")
    public R delete(@RequestBody String[] ids) {
        contractReceiptService.deleteBatch(ids);
        return R.ok();
    }

    //统计收付款总额
    @RequestMapping("/queryPaymenAmount")
    public R queryPaymenAmount(@RequestBody ContractReceiptEntity contractReceipt) {
        ContractReceiptEntity receiptEntity = contractReceiptService.queryPaymenAmount(contractReceipt);
        return R.ok().put("contractReceipt", receiptEntity);
    }

    //合同列表加载第一层树
    @RequestMapping("/getContractReceipt")
    public R getContractReceipt(@RequestParam Map<String, Object> params) {
        Map<String, Object> map = contractReceiptService.getContractReceipt(params);
        return R.ok(map);
    }

    //合同列表加载第二层树
    @RequestMapping("/getContractPayment")
    public R getContractPayment(@RequestParam Map<String, Object> params) {
        Map<String, Object> map = contractReceiptService.getContractPayment(params);
        return R.ok(map);
    }

    //删除收款合同，同时删除该合同下的付款合同
    @RequestMapping("/deleteContractReceipt")
    public R deleteContractReceipt(@RequestBody Map<String, Object> params) {
        contractReceiptService.deleteContractReceipt(params);
        ContractReceiptEntity contractReceiptEntity = new ContractReceiptEntity();
        contractReceiptEntity.setProjectId((String) params.get("projectId"));
        contractReceiptService.maintainPaymentRatio(contractReceiptEntity);
        return R.ok();
    }

    //删除付款合同，同时删除该合同下的支付信息
    @RequestMapping("/deleteContractPayment")
    public R deleteContractPayment(@RequestBody Map<String, Object> params) {
        contractReceiptService.deleteContractPayment(params);
        ContractReceiptEntity contractReceiptEntity = new ContractReceiptEntity();
        contractReceiptEntity.setProjectId((String) params.get("projectId"));
        contractReceiptService.maintainPaymentRatio(contractReceiptEntity);
        return R.ok();
    }

    //删除支付信息
    @RequestMapping("/deletePaymentProportionInfo")
    public R deletePaymentProportionInfo(@RequestBody Map<String, Object> params) {
        contractReceiptService.deletePaymentProportionInfo(params);
        ContractReceiptEntity contractReceiptEntity = new ContractReceiptEntity();
        contractReceiptEntity.setProjectId((String) params.get("projectId"));
        contractReceiptService.maintainPaymentRatio(contractReceiptEntity);
        return R.ok();
    }

    //导出合同及支付详情
    @RequestMapping("/export")
    public void export(HttpServletRequest request, HttpServletResponse response) throws IOException {
        //获取页面入参
        String projectId = request.getParameter("projectId");
        //转换为map格式
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("projectId", projectId);
        List<ContractReceiptEntity> dataMap = contractReceiptService.exportContractOrProportionInfo(params);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
        if (dataMap != null && dataMap.size() > 0) {

            //创建Excel工作薄
            Workbook book = null;
            try {
                book = new XSSFWorkbook();//excell2007
            } catch (Exception e) {
                book = new HSSFWorkbook();//excell2003
            }
            for (int i = 0; i < dataMap.size(); i++) {
                //表内容默认从工作表第三行开始;
                int start = 5;
                // 在Excel工作薄中建一张工作表;
                Sheet sheet = book.createSheet(dataMap.get(i).getContractType());
                //设置标题和表头;
                createTitle(book, sheet, "收付款详情");
                createHeads(book, sheet);
                ContractReceiptEntity contractReceipt = dataMap.get(i);
                Row rows = sheet.createRow(2);
                Cell cell01 = rows.createCell(0);
                Cell cell02 = rows.createCell(1);
                Cell cell03 = rows.createCell(2);
                Cell cell04 = rows.createCell(3);
                Cell cell05 = rows.createCell(4);
                Cell cell06 = rows.createCell(5);
                Cell cell07 = rows.createCell(6);
                cell01.setCellValue(contractReceipt.getContractType() != null ? contractReceipt.getContractType() : "");
                cell02.setCellValue(contractReceipt.getReportingUnit() != null ? contractReceipt.getReportingUnit() : "");
                cell03.setCellValue(contractReceipt.getContractTotal() != null ? contractReceipt.getContractTotal().stripTrailingZeros().toPlainString() != null ? contractReceipt.getContractTotal().stripTrailingZeros().toPlainString() : "" : "");
                cell04.setCellValue(contractReceipt.getSettlementMoney() != null ? contractReceipt.getSettlementMoney().stripTrailingZeros().toPlainString() != null ? contractReceipt.getSettlementMoney().stripTrailingZeros().toPlainString() : "" : "");
                cell05.setCellValue(contractReceipt.getTenderBatch() != null ? contractReceipt.getTenderBatch() : "");
                cell06.setCellValue(contractReceipt.getReceiptMoney() != null ? contractReceipt.getReceiptMoney().stripTrailingZeros().toPlainString() != null ? contractReceipt.getReceiptMoney().stripTrailingZeros().toPlainString() : "" : "");
                if (contractReceipt.getContractTotal() == null && contractReceipt.getReceiptMoney() == null) {
                    cell07.setCellValue("");
                } else {
                    cell07.setCellValue(BigDecimalUtils.stringToDate(contractReceipt.getReceiptMoney() != null ? contractReceipt.getReceiptMoney() : new BigDecimal(0), contractReceipt.getContractTotal() != null ? contractReceipt.getContractTotal() : new BigDecimal(0)));
                }

                List<ContractPaymentEntity> contractPaymentList = contractReceipt.getContractPaymentList();
                if (contractPaymentList != null && contractPaymentList.size() > 0) {
                    createHead(book, sheet);
                    for (int a = 0; a < contractPaymentList.size(); a++) {
                        ContractPaymentEntity contractPayment = contractPaymentList.get(a);
                        List<PaymentInfoEntity> paymentInfoList = contractPayment.getPaymentInfoList();
                        if (paymentInfoList != null && paymentInfoList.size() > 0) {
                            for (int l = 0; l < paymentInfoList.size(); l++) {
                                PaymentInfoEntity paymentInfo = paymentInfoList.get(l);
                                //一级目录
                                Row row = sheet.createRow(start + l);
                                Cell cell0 = row.createCell(0);
                                Cell cell1 = row.createCell(1);
                                Cell cell2 = row.createCell(2);
                                Cell cell3 = row.createCell(3);
                                Cell cell4 = row.createCell(4);
                                Cell cell5 = row.createCell(5);
                                Cell cell6 = row.createCell(6);
                                Cell cell7 = row.createCell(7);
                                Cell cell8 = row.createCell(8);
                                cell0.setCellValue(contractPayment.getContractNumber() != null ? contractPayment.getContractNumber() : "");
                                cell1.setCellValue(contractPayment.getContractName() != null ? contractPayment.getContractName() : "");
                                cell2.setCellValue(contractPayment.getReportingUnit() != null ? contractPayment.getReportingUnit() : "");
                                cell3.setCellValue(contractPayment.getContractTotal() != null ? contractPayment.getContractTotal().stripTrailingZeros().toPlainString() != null ? contractPayment.getContractTotal().stripTrailingZeros().toPlainString() : "" : "");
                                cell4.setCellValue(contractPayment.getSettlementMoney() != null ? contractPayment.getSettlementMoney().stripTrailingZeros().toPlainString() != null ? contractPayment.getSettlementMoney().stripTrailingZeros().toPlainString() : "" : "");
                                cell5.setCellValue(contractPayment.getTenderBatch() != null ? contractPayment.getTenderBatch() : "");
                                cell6.setCellValue(contractPayment.getContractTime() != null ? sdf.format(contractPayment.getContractTime()) : "");
                                cell7.setCellValue(contractPayment.getPaymentMoney() != null ? contractPayment.getPaymentMoney().stripTrailingZeros().toPlainString() != null ? contractPayment.getPaymentMoney().stripTrailingZeros().toPlainString() : "" : "");
                                if (contractPayment.getPaymentMoney() == null && contractPayment.getContractTotal() == null) {
                                    cell8.setCellValue("");
                                } else {
                                    cell8.setCellValue(BigDecimalUtils.stringToDate(contractPayment.getPaymentMoney() != null ? contractPayment.getPaymentMoney() : new BigDecimal(0), contractPayment.getContractTotal() != null ? contractPayment.getContractTotal() : new BigDecimal(0)));
                                }

                                //二级目录

                                /*Cell cell9 = row.createCell(9);*/
                                Cell cell9 = row.createCell(9);
                                Cell cell10 = row.createCell(10);
                                Cell cell11 = row.createCell(11);
                                // cell9.setCellValue(paymentInfo.getPaymentNumber() != null ? paymentInfo.getPaymentNumber() : "");
                                cell9.setCellValue(paymentInfo.getPaymentMoney() != null ? paymentInfo.getPaymentMoney().stripTrailingZeros().toPlainString() != null ? paymentInfo.getPaymentMoney().stripTrailingZeros().toPlainString() : "" : "");
                                cell10.setCellValue(paymentInfo.getPaymentTime() != null ? sdf.format(paymentInfo.getPaymentTime()) : "");
                                if (contractPayment.getContractTotal() == null && paymentInfo.getPaymentMoney() == null) {
                                    cell11.setCellValue("");
                                } else {
                                    cell11.setCellValue(BigDecimalUtils.stringToDate(paymentInfo.getPaymentMoney() != null ? paymentInfo.getPaymentMoney() : new BigDecimal(0), contractPayment.getContractTotal() != null ? contractPayment.getContractTotal() : new BigDecimal(0)));
                                }
                            }
                            if (paymentInfoList != null && paymentInfoList.size() > 0) {
                                sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 0, 0));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 1, 1));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 2, 2));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 3, 3));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 4, 4));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 5, 5));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 6, 6));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 7, 7));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 8, 8));
                                start += paymentInfoList.size();
                            }
                        } else {
                            //一级目录
                            Row row = sheet.createRow(start + a);
                            Cell cell0 = row.createCell(0);
                            Cell cell1 = row.createCell(1);
                            Cell cell2 = row.createCell(2);
                            Cell cell3 = row.createCell(3);
                            Cell cell4 = row.createCell(4);
                            Cell cell5 = row.createCell(5);
                            Cell cell6 = row.createCell(6);
                            Cell cell7 = row.createCell(7);
                            Cell cell8 = row.createCell(8);
                            cell0.setCellValue(contractPayment.getContractNumber() != null ? contractPayment.getContractNumber() : "");
                            cell1.setCellValue(contractPayment.getContractName() != null ? contractPayment.getContractName() : "");
                            cell2.setCellValue(contractPayment.getReportingUnit() != null ? contractPayment.getReportingUnit() : "");
                            cell3.setCellValue(contractPayment.getContractTotal() != null ? contractPayment.getContractTotal().stripTrailingZeros().toPlainString() != null ? contractPayment.getContractTotal().stripTrailingZeros().toPlainString() : "" : "");
                            cell4.setCellValue(contractPayment.getSettlementMoney() != null ? contractPayment.getSettlementMoney().stripTrailingZeros().toPlainString() != null ? contractPayment.getSettlementMoney().stripTrailingZeros().toPlainString() : "" : "");
                            cell5.setCellValue(contractPayment.getTenderBatch() != null ? contractPayment.getTenderBatch() : "");
                            cell6.setCellValue(contractPayment.getContractTime() != null ? sdf.format(contractPayment.getContractTime()) : "");
                            cell7.setCellValue(contractPayment.getPaymentMoney() != null ? contractPayment.getPaymentMoney().stripTrailingZeros().toPlainString() != null ? contractPayment.getPaymentMoney().stripTrailingZeros().toPlainString() : "" : "");
                            if (contractPayment.getPaymentMoney() == null && contractPayment.getContractTotal() == null) {
                                cell8.setCellValue("");
                            } else {
                                cell8.setCellValue(BigDecimalUtils.stringToDate(contractPayment.getPaymentMoney() != null ? contractPayment.getPaymentMoney() : new BigDecimal(0), contractPayment.getContractTotal() != null ? contractPayment.getContractTotal() : new BigDecimal(0)));
                            }
                        }
                    }
                }
            }
            String msg = new String(("合计及支付详情.xls").getBytes(), "ISO-8859-1");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.addHeader("Content-disposition", "attachment;filename=" + msg);
            OutputStream os = response.getOutputStream();
            // 写入数据 把相应的Excel 工作簿存盘
            book.write(os);
            os.flush();
            os.close();
        }

    }

    //导出合同及支付详情
    @RequestMapping("/exportContract")
    public void exportContract(HttpServletRequest request, HttpServletResponse response) throws IOException {
        //获取页面入参
        String projectId = request.getParameter("projectId");
        //转换为map格式
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("projectId", projectId);
        List<ContractReceiptEntity> dataMap = contractReceiptService.exportContractOrProportionInfo(params);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
        if (dataMap != null && dataMap.size() > 0) {
            //创建Excel工作薄
            Workbook book = null;
            try {
                book = new XSSFWorkbook();//excell2007
            } catch (Exception e) {
                book = new HSSFWorkbook();//excell2003
            }
            //表内容默认从工作表第三行开始;
            int start = 2;
            // 在Excel工作薄中建一张工作表;
            Sheet sheet = book.createSheet(sheetTitle);
            //设置标题和表头;
            createTitle(book, sheet, "收付款详情");
            createHead(book, sheet);
            for (int i = 0; i < dataMap.size(); i++) {
                ContractReceiptEntity contractReceipt = dataMap.get(i);
                List<ContractPaymentEntity> contractPaymentList = contractReceipt.getContractPaymentList();
                if (contractPaymentList != null && contractPaymentList.size() > 0) {
                    for (int a = 0; a < contractPaymentList.size(); a++) {
                        ContractPaymentEntity contractPayment = contractPaymentList.get(a);
                        List<PaymentInfoEntity> paymentInfoList = contractPayment.getPaymentInfoList();
                        if (paymentInfoList != null && paymentInfoList.size() > 0) {
                            for (int l = 0; l < paymentInfoList.size(); l++) {
                                PaymentInfoEntity paymentInfo = paymentInfoList.get(l);
                                Row row = sheet.createRow(start + l);
                                Cell cell0 = row.createCell(0);
                                Cell cell1 = row.createCell(1);
                                Cell cell2 = row.createCell(2);
                                Cell cell3 = row.createCell(3);
                                Cell cell4 = row.createCell(4);
                                Cell cell5 = row.createCell(5);
                                Cell cell6 = row.createCell(6);
                                cell0.setCellValue(contractReceipt.getContractType() != null ? contractReceipt.getContractType() : "");
                                cell1.setCellValue(contractReceipt.getReportingUnit() != null ? contractReceipt.getReportingUnit() : "");
                                cell2.setCellValue(contractReceipt.getContractTotal() != null ? contractReceipt.getContractTotal().stripTrailingZeros().toPlainString() != null ? contractReceipt.getContractTotal().stripTrailingZeros().toPlainString() : "" : "");
                                cell3.setCellValue(contractReceipt.getSettlementMoney() != null ? contractReceipt.getSettlementMoney().stripTrailingZeros().toPlainString() != null ? contractReceipt.getSettlementMoney().stripTrailingZeros().toPlainString() : "" : "");
                                cell4.setCellValue(contractReceipt.getTenderBatch() != null ? contractReceipt.getTenderBatch() : "");
                                cell5.setCellValue(contractReceipt.getReceiptMoney() != null ? contractReceipt.getReceiptMoney().stripTrailingZeros().toPlainString() != null ? contractReceipt.getReceiptMoney().stripTrailingZeros().toPlainString() : "" : "");
                                if (contractReceipt.getContractTotal() == null && contractReceipt.getReceiptMoney() == null) {
                                    cell6.setCellValue("");
                                } else {
                                    cell6.setCellValue(BigDecimalUtils.stringToDate(contractReceipt.getReceiptMoney() != null ? contractReceipt.getReceiptMoney() : new BigDecimal(0), contractReceipt.getContractTotal() != null ? contractReceipt.getContractTotal() : new BigDecimal(0)));
                                }
                                Cell cell7 = row.createCell(7);
                                Cell cell8 = row.createCell(8);
                                Cell cell9 = row.createCell(9);
                                Cell cell10 = row.createCell(10);
                                Cell cell11 = row.createCell(11);
                                Cell cell12 = row.createCell(12);
                                Cell cell13 = row.createCell(13);
                                Cell cell14 = row.createCell(14);
                                Cell cell15 = row.createCell(15);
                                cell7.setCellValue(contractPayment.getContractNumber() != null ? contractPayment.getContractNumber() : "");
                                cell8.setCellValue(contractPayment.getContractName() != null ? contractPayment.getContractName() : "");
                                cell9.setCellValue(contractPayment.getReportingUnit() != null ? contractPayment.getReportingUnit() : "");
                                cell10.setCellValue(contractPayment.getContractTotal() != null ? contractPayment.getContractTotal().stripTrailingZeros().toPlainString() != null ? contractPayment.getContractTotal().stripTrailingZeros().toPlainString() : "" : "");
                                cell11.setCellValue(contractPayment.getSettlementMoney() != null ? contractPayment.getSettlementMoney().stripTrailingZeros().toPlainString() != null ? contractPayment.getSettlementMoney().stripTrailingZeros().toPlainString() : "" : "");
                                cell12.setCellValue(contractPayment.getTenderBatch() != null ? contractPayment.getTenderBatch() : "");
                                cell13.setCellValue(contractPayment.getContractTime() != null ? sdf.format(contractPayment.getContractTime()) : "");
                                cell14.setCellValue(contractPayment.getPaymentMoney() != null ? contractPayment.getPaymentMoney().stripTrailingZeros().toPlainString() != null ? contractPayment.getPaymentMoney().stripTrailingZeros().toPlainString() : "" : "");
                                if (contractPayment.getPaymentMoney() == null && contractPayment.getContractTotal() == null) {
                                    cell15.setCellValue("");
                                } else {
                                    cell15.setCellValue(BigDecimalUtils.stringToDate(contractPayment.getPaymentMoney() != null ? contractPayment.getPaymentMoney() : new BigDecimal(0), contractPayment.getContractTotal() != null ? contractPayment.getContractTotal() : new BigDecimal(0)));
                                }


                                Cell cell16 = row.createCell(16);
                                Cell cell17 = row.createCell(17);
                                Cell cell18 = row.createCell(18);
                                Cell cell19 = row.createCell(19);
                                cell16.setCellValue(paymentInfo.getPaymentNumber() != null ? paymentInfo.getPaymentNumber() : "");
                                cell17.setCellValue(paymentInfo.getPaymentMoney() != null ? paymentInfo.getPaymentMoney().stripTrailingZeros().toPlainString() != null ? paymentInfo.getPaymentMoney().stripTrailingZeros().toPlainString() : "" : "");
                                cell18.setCellValue(paymentInfo.getPaymentTime() != null ? sdf.format(paymentInfo.getPaymentTime()) : "");
                                if (contractPayment.getContractTotal() == null && paymentInfo.getPaymentMoney() == null) {
                                    cell19.setCellValue("");
                                } else {
                                    cell19.setCellValue(BigDecimalUtils.stringToDate(paymentInfo.getPaymentMoney() != null ? paymentInfo.getPaymentMoney() : new BigDecimal(0), contractPayment.getContractTotal() != null ? contractPayment.getContractTotal() : new BigDecimal(0)));
                                }


                            }


                            if (paymentInfoList.size() > 0) {
                                sheet.addMergedRegion(new CellRangeAddress(start, start + contractReceipt.getSumPaymentInfo() - 1, 0, 0));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + contractReceipt.getSumPaymentInfo() - 1, 1, 1));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + contractReceipt.getSumPaymentInfo() - 1, 2, 2));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + contractReceipt.getSumPaymentInfo() - 1, 3, 3));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + contractReceipt.getSumPaymentInfo() - 1, 4, 4));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + contractReceipt.getSumPaymentInfo() - 1, 5, 5));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + contractReceipt.getSumPaymentInfo() - 1, 6, 6));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 7, 7));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 8, 8));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 9, 9));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 10, 10));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 11, 11));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 12, 12));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 13, 13));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 14, 14));
                                sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 15, 15));

                                System.out.println(start);
                            }

                        } else {
                            Row row = sheet.createRow(start + a);
                            Cell cell0 = row.createCell(0);
                            Cell cell1 = row.createCell(1);
                            Cell cell2 = row.createCell(2);
                            Cell cell3 = row.createCell(3);
                            Cell cell4 = row.createCell(4);
                            Cell cell5 = row.createCell(5);
                            Cell cell6 = row.createCell(6);
                            cell0.setCellValue(contractReceipt.getContractType() != null ? contractReceipt.getContractType() : "");
                            cell1.setCellValue(contractReceipt.getReportingUnit() != null ? contractReceipt.getReportingUnit() : "");
                            cell2.setCellValue(contractReceipt.getContractTotal() != null ? contractReceipt.getContractTotal().stripTrailingZeros().toPlainString() != null ? contractReceipt.getContractTotal().stripTrailingZeros().toPlainString() : "" : "");
                            cell3.setCellValue(contractReceipt.getSettlementMoney() != null ? contractReceipt.getSettlementMoney().stripTrailingZeros().toPlainString() != null ? contractReceipt.getSettlementMoney().stripTrailingZeros().toPlainString() : "" : "");
                            cell4.setCellValue(contractReceipt.getTenderBatch() != null ? contractReceipt.getTenderBatch() : "");
                            cell5.setCellValue(contractReceipt.getReceiptMoney() != null ? contractReceipt.getReceiptMoney().stripTrailingZeros().toPlainString() != null ? contractReceipt.getReceiptMoney().stripTrailingZeros().toPlainString() : "" : "");
                            if (contractReceipt.getContractTotal() == null && contractReceipt.getReceiptMoney() == null) {
                                cell6.setCellValue("");
                            } else {
                                cell6.setCellValue(BigDecimalUtils.stringToDate(contractReceipt.getReceiptMoney() != null ? contractReceipt.getReceiptMoney() : new BigDecimal(0), contractReceipt.getContractTotal() != null ? contractReceipt.getContractTotal() : new BigDecimal(0)));
                            }
                            Cell cell7 = row.createCell(7);
                            Cell cell8 = row.createCell(8);
                            Cell cell9 = row.createCell(9);
                            Cell cell10 = row.createCell(10);
                            Cell cell11 = row.createCell(11);
                            Cell cell12 = row.createCell(12);
                            Cell cell13 = row.createCell(13);
                            Cell cell14 = row.createCell(14);
                            Cell cell15 = row.createCell(15);
                            cell7.setCellValue(contractPayment.getContractNumber() != null ? contractPayment.getContractNumber() : "");
                            cell8.setCellValue(contractPayment.getContractName() != null ? contractPayment.getContractName() : "");
                            cell9.setCellValue(contractPayment.getReportingUnit() != null ? contractPayment.getReportingUnit() : "");
                            cell10.setCellValue(contractPayment.getContractTotal() != null ? contractPayment.getContractTotal().stripTrailingZeros().toPlainString() != null ? contractPayment.getContractTotal().stripTrailingZeros().toPlainString() : "" : "");
                            cell11.setCellValue(contractPayment.getSettlementMoney() != null ? contractPayment.getSettlementMoney().stripTrailingZeros().toPlainString() != null ? contractPayment.getSettlementMoney().stripTrailingZeros().toPlainString() : "" : "");
                            cell12.setCellValue(contractPayment.getTenderBatch() != null ? contractPayment.getTenderBatch() : "");
                            cell13.setCellValue(contractPayment.getContractTime() != null ? sdf.format(contractPayment.getContractTime()) : "");
                            cell14.setCellValue(contractPayment.getPaymentMoney() != null ? contractPayment.getPaymentMoney().stripTrailingZeros().toPlainString() != null ? contractPayment.getPaymentMoney().stripTrailingZeros().toPlainString() : "" : "");
                            if (contractPayment.getPaymentMoney() == null && contractPayment.getContractTotal() == null) {
                                cell15.setCellValue("");
                            } else {
                                cell15.setCellValue(BigDecimalUtils.stringToDate(contractPayment.getPaymentMoney() != null ? contractPayment.getPaymentMoney() : new BigDecimal(0), contractPayment.getContractTotal() != null ? contractPayment.getContractTotal() : new BigDecimal(0)));
                            }
                        }
                        start += contractReceipt.getSumPaymentInfo();
                        if (contractPaymentList.size() > 0) {
                            sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() - 1, 0, 0));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() - 1, 1, 1));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() - 1, 2, 2));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() - 1, 3, 3));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() - 1, 4, 4));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() - 1, 5, 5));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() - 1, 6, 6));
                            start += contractPaymentList.size();
                        }
                    }
                } else {
                    //一级目录
                    Row row = sheet.createRow(start + 1);
                    Cell cell0 = row.createCell(0);
                    Cell cell1 = row.createCell(1);
                    Cell cell2 = row.createCell(2);
                    Cell cell3 = row.createCell(3);
                    Cell cell4 = row.createCell(4);
                    Cell cell5 = row.createCell(5);
                    Cell cell6 = row.createCell(6);
                    cell0.setCellValue(contractReceipt.getContractType() != null ? contractReceipt.getContractType() : "");
                    cell1.setCellValue(contractReceipt.getReportingUnit() != null ? contractReceipt.getReportingUnit() : "");
                    cell2.setCellValue(contractReceipt.getContractTotal() != null ? contractReceipt.getContractTotal().stripTrailingZeros().toPlainString() != null ? contractReceipt.getContractTotal().stripTrailingZeros().toPlainString() : "" : "");
                    cell3.setCellValue(contractReceipt.getSettlementMoney() != null ? contractReceipt.getSettlementMoney().stripTrailingZeros().toPlainString() != null ? contractReceipt.getSettlementMoney().stripTrailingZeros().toPlainString() : "" : "");
                    cell4.setCellValue(contractReceipt.getTenderBatch() != null ? contractReceipt.getTenderBatch() : "");
                    cell5.setCellValue(contractReceipt.getReceiptMoney() != null ? contractReceipt.getReceiptMoney().stripTrailingZeros().toPlainString() != null ? contractReceipt.getReceiptMoney().stripTrailingZeros().toPlainString() : "" : "");
                    if (contractReceipt.getContractTotal() == null && contractReceipt.getReceiptMoney() == null) {
                        cell6.setCellValue("");
                    } else {
                        cell6.setCellValue(BigDecimalUtils.stringToDate(contractReceipt.getReceiptMoney() != null ? contractReceipt.getReceiptMoney() : new BigDecimal(0), contractReceipt.getContractTotal() != null ? contractReceipt.getContractTotal() : new BigDecimal(0)));
                    }
                }
            }

            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.addHeader("Content-disposition", "attachment;filename=" + "合计及支付详情");
            // 写入数据 把相应的Excel 工作簿存盘
            OutputStream os = response.getOutputStream();
            book.write(os);
            os.flush();
            os.close();
        }
    }

    //导出合同及支付详情
    @RequestMapping("/exportContractOrProportion")
    public void exportContractOrProportion(HttpServletRequest request, HttpServletResponse response) throws IOException {
        //获取页面入参
        String projectId = request.getParameter("projectId");
        //转换为map格式
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("projectId", projectId);
        List<ContractReceiptEntity> dataMap = contractReceiptService.exportContractOrProportionInfo(params);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
        if (dataMap != null && dataMap.size() > 0) {
            //创建Excel工作薄
            Workbook book = null;
            try {
                book = new XSSFWorkbook();//excell2007
            } catch (Exception e) {
                book = new HSSFWorkbook();//excell2003
            }
            //表内容默认从工作表第三行开始;
            int start = 2;
            // 在Excel工作薄中建一张工作表;
            Sheet sheet = book.createSheet(sheetTitle);
            //设置标题和表头;
            createTitle(book, sheet, "收付款详情");
            createHead(book, sheet);
            for (int i = 0; i < dataMap.size(); i++) {
                ContractReceiptEntity contractReceipt = dataMap.get(i);
                List<ContractPaymentEntity> contractPaymentList = contractReceipt.getContractPaymentList();
                if (contractPaymentList != null && contractPaymentList.size() > 0) {
                    for (int a = 0; a < contractPaymentList.size(); a++) {
                        ContractPaymentEntity contractPayment = contractPaymentList.get(a);
                        List<PaymentInfoEntity> paymentInfoList = contractPayment.getPaymentInfoList();
                        if (paymentInfoList != null && paymentInfoList.size() > 0) {
                            for (int l = 0; l < paymentInfoList.size(); l++) {
                                Row row = sheet.createRow(start + l);
                                PaymentInfoEntity paymentInfo = paymentInfoList.get(l);
                                Cell cell0 = row.createCell(0);
                                Cell cell1 = row.createCell(1);
                                Cell cell2 = row.createCell(2);
                                Cell cell3 = row.createCell(3);
                                Cell cell4 = row.createCell(4);
                                Cell cell5 = row.createCell(5);
                                Cell cell6 = row.createCell(6);
                                Cell cell7 = row.createCell(7);
                                Cell cell8 = row.createCell(8);
                                Cell cell9 = row.createCell(9);
                                Cell cell10 = row.createCell(10);
                                Cell cell11 = row.createCell(11);
                                Cell cell12 = row.createCell(12);
                                Cell cell13 = row.createCell(13);
                                Cell cell14 = row.createCell(14);
                                Cell cell15 = row.createCell(15);
                                Cell cell16 = row.createCell(16);
                                Cell cell17 = row.createCell(17);
                                Cell cell18 = row.createCell(18);
                                Cell cell19 = row.createCell(19);
                                //一级目录
                                cell0.setCellValue(contractReceipt.getContractType() != null ? contractReceipt.getContractType() : "");
                                cell1.setCellValue(contractReceipt.getReportingUnit() != null ? contractReceipt.getReportingUnit() : "");
                                cell2.setCellValue(contractReceipt.getContractTotal() != null ? contractReceipt.getContractTotal().stripTrailingZeros().toPlainString() != null ? contractReceipt.getContractTotal().stripTrailingZeros().toPlainString() : "" : "");
                                cell3.setCellValue(contractReceipt.getSettlementMoney() != null ? contractReceipt.getSettlementMoney().stripTrailingZeros().toPlainString() != null ? contractReceipt.getSettlementMoney().stripTrailingZeros().toPlainString() : "" : "");
                                cell4.setCellValue(contractReceipt.getTenderBatch() != null ? contractReceipt.getTenderBatch() : "");
                                cell5.setCellValue(contractReceipt.getReceiptMoney() != null ? contractReceipt.getReceiptMoney().stripTrailingZeros().toPlainString() != null ? contractReceipt.getReceiptMoney().stripTrailingZeros().toPlainString() : "" : "");
                                if (contractReceipt.getContractTotal() == null && contractReceipt.getReceiptMoney() == null) {
                                    cell6.setCellValue("");
                                } else {
                                    cell6.setCellValue(BigDecimalUtils.stringToDate(contractReceipt.getReceiptMoney() != null ? contractReceipt.getReceiptMoney() : new BigDecimal(0), contractReceipt.getContractTotal() != null ? contractReceipt.getContractTotal() : new BigDecimal(0)));
                                }
                                //二级目录
                                cell7.setCellValue(contractPayment.getContractNumber() != null ? contractPayment.getContractNumber() : "");
                                cell8.setCellValue(contractPayment.getContractName() != null ? contractPayment.getContractName() : "");
                                cell9.setCellValue(contractPayment.getReportingUnit() != null ? contractPayment.getReportingUnit() : "");
                                cell10.setCellValue(contractPayment.getContractTotal() != null ? contractPayment.getContractTotal().stripTrailingZeros().toPlainString() != null ? contractPayment.getContractTotal().stripTrailingZeros().toPlainString() : "" : "");
                                cell11.setCellValue(contractPayment.getSettlementMoney() != null ? contractPayment.getSettlementMoney().stripTrailingZeros().toPlainString() != null ? contractPayment.getSettlementMoney().stripTrailingZeros().toPlainString() : "" : "");
                                cell12.setCellValue(contractPayment.getTenderBatch() != null ? contractPayment.getTenderBatch() : "");
                                cell13.setCellValue(contractPayment.getContractTime() != null ? sdf.format(contractPayment.getContractTime()) : "");
                                cell14.setCellValue(contractPayment.getPaymentMoney() != null ? contractPayment.getPaymentMoney().stripTrailingZeros().toPlainString() != null ? contractPayment.getPaymentMoney().stripTrailingZeros().toPlainString() : "" : "");
                                if (contractPayment.getPaymentMoney() == null && contractPayment.getContractTotal() == null) {
                                    cell15.setCellValue("");
                                } else {
                                    cell15.setCellValue(BigDecimalUtils.stringToDate(contractPayment.getPaymentMoney() != null ? contractPayment.getPaymentMoney() : new BigDecimal(0), contractPayment.getContractTotal() != null ? contractPayment.getContractTotal() : new BigDecimal(0)));
                                }

                                //三级目录
                                cell16.setCellValue(paymentInfo.getPaymentNumber() != null ? paymentInfo.getPaymentNumber() : "");
                                cell17.setCellValue(paymentInfo.getPaymentMoney() != null ? paymentInfo.getPaymentMoney().stripTrailingZeros().toPlainString() != null ? paymentInfo.getPaymentMoney().stripTrailingZeros().toPlainString() : "" : "");
                                cell18.setCellValue(paymentInfo.getPaymentTime() != null ? sdf.format(paymentInfo.getPaymentTime()) : "");
                                if (contractPayment.getContractTotal() == null && paymentInfo.getPaymentMoney() == null) {
                                    cell19.setCellValue("");
                                } else {
                                    cell19.setCellValue(BigDecimalUtils.stringToDate(paymentInfo.getPaymentMoney() != null ? paymentInfo.getPaymentMoney() : new BigDecimal(0), contractPayment.getContractTotal() != null ? contractPayment.getContractTotal() : new BigDecimal(0)));
                                }
								/*//设置单元格样式;
								CellStyle contentStyle = createContentStyle(book);
								CellStyle otherStyle = createOtherStyle(book);
								//前8列背景、字体、边框、对齐方式;
								for (int h = 0; h < 6; h++) {
									row.getCell(h).setCellStyle(contentStyle);
								}
								//后4列背景、字体、边框、对齐方式、日期格式;
								for (int s = 6; s < cols; s++) {
									if (s == 13) {
										row.getCell(13).setCellStyle(setDateStyle(book));//设置日期样式
										//System.out.println(DateUtil.isCellDateFormatted(row.getCell(8)));//输出：true
									}
									if (s == 18) {
										row.getCell(18).setCellStyle(setDateStyle(book));//设置日期样式
										//System.out.println(DateUtil.isCellDateFormatted(row.getCell(8)));//输出：true
									}
									row.getCell(s).setCellStyle(otherStyle);
								}
								//单元格列宽度;
								for (int h = 0; h < cols; h++) {
									if (h == 2 || h == 3 || h == 5 || h == 10 || h == 11) {
										sheet.setColumnWidth(h, 40 * 256);//设置第一类列的宽度是44个字符宽度
									} else if (h == 10) {
										sheet.setColumnWidth(h, 60 * 256);
									} else {
										sheet.setColumnWidth(h, 16 * 256);//设置第一类列的宽度是16个字符宽度,水平居中;
										CellStyle cellStyle = book.createCellStyle();
										cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
									}
								}*/


                            }
                            sheet.addMergedRegion(new CellRangeAddress(start, start + contractReceipt.getSumPaymentInfo() - 1, 0, 0));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + contractReceipt.getSumPaymentInfo() - 1, 1, 1));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + contractReceipt.getSumPaymentInfo() - 1, 2, 2));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + contractReceipt.getSumPaymentInfo() - 1, 3, 3));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + contractReceipt.getSumPaymentInfo() - 1, 4, 4));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + contractReceipt.getSumPaymentInfo() - 1, 5, 5));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + contractReceipt.getSumPaymentInfo() - 1, 6, 6));
                            //前5列根据每一项重点工作的任务及目标分解数量来决定跨行数;
                            sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 7, 7));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 8, 8));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 9, 9));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 10, 10));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 11, 11));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 12, 12));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 13, 13));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 14, 14));
                            sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 15, 15));
                            start++;

                        } else {
                            //一级目录
                            Row row = sheet.createRow(start + a);
                            Cell cell0 = row.createCell(0);
                            Cell cell1 = row.createCell(1);
                            Cell cell2 = row.createCell(2);
                            Cell cell3 = row.createCell(3);
                            Cell cell4 = row.createCell(4);
                            Cell cell5 = row.createCell(5);
                            Cell cell6 = row.createCell(6);
                            cell0.setCellValue(contractReceipt.getContractType() != null ? contractReceipt.getContractType() : "");
                            cell1.setCellValue(contractReceipt.getReportingUnit() != null ? contractReceipt.getReportingUnit() : "");
                            cell2.setCellValue(contractReceipt.getContractTotal() != null ? contractReceipt.getContractTotal().stripTrailingZeros().toPlainString() != null ? contractReceipt.getContractTotal().stripTrailingZeros().toPlainString() : "" : "");
                            cell3.setCellValue(contractReceipt.getSettlementMoney() != null ? contractReceipt.getSettlementMoney().stripTrailingZeros().toPlainString() != null ? contractReceipt.getSettlementMoney().stripTrailingZeros().toPlainString() : "" : "");
                            cell4.setCellValue(contractReceipt.getTenderBatch() != null ? contractReceipt.getTenderBatch() : "");
                            cell5.setCellValue(contractReceipt.getReceiptMoney() != null ? contractReceipt.getReceiptMoney().stripTrailingZeros().toPlainString() != null ? contractReceipt.getReceiptMoney().stripTrailingZeros().toPlainString() : "" : "");
                            if (contractReceipt.getContractTotal() == null && contractReceipt.getReceiptMoney() == null) {
                                cell6.setCellValue("");
                            } else {
                                cell6.setCellValue(BigDecimalUtils.stringToDate(contractReceipt.getReceiptMoney() != null ? contractReceipt.getReceiptMoney() : new BigDecimal(0), contractReceipt.getContractTotal() != null ? contractReceipt.getContractTotal() : new BigDecimal(0)));
                            }
                            //二级目录
                            Cell cell7 = row.createCell(7);
                            Cell cell8 = row.createCell(8);
                            Cell cell9 = row.createCell(9);
                            Cell cell10 = row.createCell(10);
                            Cell cell11 = row.createCell(11);
                            Cell cell12 = row.createCell(12);
                            Cell cell13 = row.createCell(13);
                            Cell cell14 = row.createCell(14);
                            Cell cell15 = row.createCell(15);
                            cell7.setCellValue(contractPayment.getContractNumber() != null ? contractPayment.getContractNumber() : "");
                            cell8.setCellValue(contractPayment.getContractName() != null ? contractPayment.getContractName() : "");
                            cell9.setCellValue(contractPayment.getReportingUnit() != null ? contractPayment.getReportingUnit() : "");
                            cell10.setCellValue(contractPayment.getContractTotal() != null ? contractPayment.getContractTotal().stripTrailingZeros().toPlainString() != null ? contractPayment.getContractTotal().stripTrailingZeros().toPlainString() : "" : "");
                            cell11.setCellValue(contractPayment.getSettlementMoney() != null ? contractPayment.getSettlementMoney().stripTrailingZeros().toPlainString() != null ? contractPayment.getSettlementMoney().stripTrailingZeros().toPlainString() : "" : "");
                            cell12.setCellValue(contractPayment.getTenderBatch() != null ? contractPayment.getTenderBatch() : "");
                            cell13.setCellValue(contractPayment.getContractTime() != null ? sdf.format(contractPayment.getContractTime()) : "");
                            cell14.setCellValue(contractPayment.getPaymentMoney() != null ? contractPayment.getPaymentMoney().stripTrailingZeros().toPlainString() != null ? contractPayment.getPaymentMoney().stripTrailingZeros().toPlainString() : "" : "");
                            if (contractPayment.getPaymentMoney() == null && contractPayment.getContractTotal() == null) {
                                cell15.setCellValue("");
                            } else {
                                cell15.setCellValue(BigDecimalUtils.stringToDate(contractPayment.getPaymentMoney() != null ? contractPayment.getPaymentMoney() : new BigDecimal(0), contractPayment.getContractTotal() != null ? contractPayment.getContractTotal() : new BigDecimal(0)));
                            }
							 /* //设置单元格样式;
							  CellStyle contentStyle = createContentStyle(book);
							  CellStyle otherStyle = createOtherStyle(book);
							  //前8列背景、字体、边框、对齐方式;
							  for (int h = 0; h < 7; h++) {
								  row.getCell(h).setCellStyle(contentStyle);
							  }
							  //后4列背景、字体、边框、对齐方式、日期格式;
							  for (int s = 7; s < cols; s++) {
								  if (s == 13) {
									  row.getCell(13).setCellStyle(setDateStyle(book));//设置日期样式
									  //System.out.println(DateUtil.isCellDateFormatted(row.getCell(8)));//输出：true
								  }
								  row.getCell(s).setCellStyle(otherStyle);
							  }
							  //单元格列宽度;
							  for (int h = 0; h < cols; h++) {
								  if (h == 2 || h == 3 || h == 5 || h == 10 || h == 11) {
									  sheet.setColumnWidth(h, 40 * 256);//设置第一类列的宽度是44个字符宽度
								  } else if (h == 10) {
									  sheet.setColumnWidth(h, 60 * 256);
								  } else {
									  sheet.setColumnWidth(h, 16 * 256);//设置第一类列的宽度是16个字符宽度,水平居中;
									  CellStyle cellStyle = book.createCellStyle();
									  cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
								  }
							  }*/

                        }

                    }
                    if (contractPaymentList.size() > 0) {
                        sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() - 1, 0, 0));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() - 1, 1, 1));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() - 1, 2, 2));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() - 1, 3, 3));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() - 1, 4, 4));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() - 1, 5, 5));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() - 1, 6, 6));
                        start += contractPaymentList.size();
                    }
                } else {
                    Row row = sheet.createRow(start + i);
                    Cell cell0 = row.createCell(0);
                    Cell cell1 = row.createCell(1);
                    Cell cell2 = row.createCell(2);
                    Cell cell3 = row.createCell(3);
                    Cell cell4 = row.createCell(4);
                    Cell cell5 = row.createCell(5);
                    Cell cell6 = row.createCell(6);
                    //一级目录
                    cell0.setCellValue(contractReceipt.getContractType() != null ? contractReceipt.getContractType() : "");
                    cell1.setCellValue(contractReceipt.getReportingUnit() != null ? contractReceipt.getReportingUnit() : "");
                    cell2.setCellValue(contractReceipt.getContractTotal() != null ? contractReceipt.getContractTotal().stripTrailingZeros().toPlainString() != null ? contractReceipt.getContractTotal().stripTrailingZeros().toPlainString() : "" : "");
                    cell3.setCellValue(contractReceipt.getSettlementMoney() != null ? contractReceipt.getSettlementMoney().stripTrailingZeros().toPlainString() != null ? contractReceipt.getSettlementMoney().stripTrailingZeros().toPlainString() : "" : "");
                    cell4.setCellValue(contractReceipt.getTenderBatch() != null ? contractReceipt.getTenderBatch() : "");
                    cell5.setCellValue(contractReceipt.getReceiptMoney() != null ? contractReceipt.getReceiptMoney().stripTrailingZeros().toPlainString() != null ? contractReceipt.getReceiptMoney().stripTrailingZeros().toPlainString() : "" : "");
                    if (contractReceipt.getContractTotal() == null && contractReceipt.getReceiptMoney() == null) {
                        cell6.setCellValue("");
                    } else {
                        cell6.setCellValue(BigDecimalUtils.stringToDate(contractReceipt.getReceiptMoney() != null ? contractReceipt.getReceiptMoney() : new BigDecimal(0), contractReceipt.getContractTotal() != null ? contractReceipt.getContractTotal() : new BigDecimal(0)));
                    }
                }

            }

            start += dataMap.size();
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.addHeader("Content-disposition", "attachment;filename=" + "合计及支付详情");
            // 写入数据 把相应的Excel 工作簿存盘
            OutputStream os = response.getOutputStream();
            book.write(os);
            os.flush();
            os.close();
        }
    }


    //导出合同及支付详情
    @RequestMapping("/exportContractOrProportionInfo")
    public void exportContractOrProportionInfo(HttpServletRequest request, HttpServletResponse response) throws IOException {
        System.out.println("*************进来了***********");
        //获取页面入参
        String projectId = request.getParameter("projectId");
        //转换为map格式
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("projectId", projectId);
        /*try {*/
        List<ContractReceiptEntity> dataMap = contractReceiptService.exportContractOrProportionInfo(params);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
        if (dataMap != null && dataMap.size() > 0) {
            //创建Excel工作薄
            Workbook book = null;
            try {
                book = new XSSFWorkbook();//excell2007
            } catch (Exception e) {
                book = new HSSFWorkbook();//excell2003
            }
            //表内容默认从工作表第三行开始;
            int start = 2;
            // 在Excel工作薄中建一张工作表;
            Sheet sheet = book.createSheet(sheetTitle);
            //设置标题和表头;
            createTitle(book, sheet, "收付款详情");
            createHead(book, sheet);
            for (int i = 0; i < dataMap.size(); i++) {
                ContractReceiptEntity contractReceipt = dataMap.get(i);
                List<ContractPaymentEntity> contractPaymentList = contractReceipt.getContractPaymentList();
                for (int k = 0; k < contractPaymentList.size(); k++) {
                    ContractPaymentEntity contractPayment = contractPaymentList.get(k);
                    List<PaymentInfoEntity> paymentInfoList = contractPayment.getPaymentInfoList();
                    for (int l = 0; l < paymentInfoList.size(); l++) {
                        Row row = sheet.createRow(start + l);
                        PaymentInfoEntity paymentInfo = paymentInfoList.get(l);
                        Cell cell0 = row.createCell(0);
                        Cell cell1 = row.createCell(1);
                        Cell cell2 = row.createCell(2);
                        Cell cell3 = row.createCell(3);
                        Cell cell4 = row.createCell(4);
                        Cell cell5 = row.createCell(5);
                        Cell cell6 = row.createCell(6);
                        Cell cell7 = row.createCell(7);
                        Cell cell8 = row.createCell(8);
                        Cell cell9 = row.createCell(9);
                        Cell cell10 = row.createCell(10);
                        Cell cell11 = row.createCell(11);
                        Cell cell12 = row.createCell(12);
                        Cell cell13 = row.createCell(13);
                        Cell cell14 = row.createCell(14);
                        Cell cell15 = row.createCell(15);
                        Cell cell16 = row.createCell(16);
                        Cell cell17 = row.createCell(17);
                        Cell cell18 = row.createCell(18);
                        Cell cell19 = row.createCell(19);
                        //一级目录
                        cell0.setCellValue(contractReceipt.getContractType() != null ? contractReceipt.getContractType() : "");
                        cell1.setCellValue(contractReceipt.getReportingUnit() != null ? contractReceipt.getReportingUnit() : "");
                        cell2.setCellValue(contractReceipt.getContractTotal() != null ? contractReceipt.getContractTotal().stripTrailingZeros().toPlainString() != null ? contractReceipt.getContractTotal().stripTrailingZeros().toPlainString() : "" : "");
                        cell3.setCellValue(contractReceipt.getSettlementMoney() != null ? contractReceipt.getSettlementMoney().stripTrailingZeros().toPlainString() != null ? contractReceipt.getSettlementMoney().stripTrailingZeros().toPlainString() : "" : "");
                        cell4.setCellValue(contractReceipt.getTenderBatch() != null ? contractReceipt.getTenderBatch() : "");
                        cell5.setCellValue(contractReceipt.getReceiptMoney() != null ? contractReceipt.getReceiptMoney().stripTrailingZeros().toPlainString() != null ? contractReceipt.getReceiptMoney().stripTrailingZeros().toPlainString() : "" : "");
                        if (contractReceipt.getContractTotal() == null && contractReceipt.getReceiptMoney() == null) {
                            cell6.setCellValue("");
                        } else {
                            cell6.setCellValue(BigDecimalUtils.stringToDate(contractReceipt.getReceiptMoney() != null ? contractReceipt.getReceiptMoney() : new BigDecimal(0), contractReceipt.getContractTotal() != null ? contractReceipt.getContractTotal() : new BigDecimal(0)));
                        }
                        //二级目录
                        cell7.setCellValue(contractPayment.getContractNumber() != null ? contractPayment.getContractNumber() : "");
                        cell8.setCellValue(contractPayment.getContractName() != null ? contractPayment.getContractName() : "");
                        cell9.setCellValue(contractPayment.getReportingUnit() != null ? contractPayment.getReportingUnit() : "");
                        cell10.setCellValue(contractPayment.getContractTotal() != null ? contractPayment.getContractTotal().stripTrailingZeros().toPlainString() != null ? contractPayment.getContractTotal().stripTrailingZeros().toPlainString() : "" : "");
                        cell11.setCellValue(contractPayment.getSettlementMoney() != null ? contractPayment.getSettlementMoney().stripTrailingZeros().toPlainString() != null ? contractPayment.getSettlementMoney().stripTrailingZeros().toPlainString() : "" : "");
                        cell12.setCellValue(contractPayment.getTenderBatch() != null ? contractPayment.getTenderBatch() : "");
                        cell13.setCellValue(contractPayment.getContractTime() != null ? sdf.format(contractPayment.getContractTime()) : "");
                        cell14.setCellValue(contractPayment.getPaymentMoney() != null ? contractPayment.getPaymentMoney().stripTrailingZeros().toPlainString() != null ? contractPayment.getPaymentMoney().stripTrailingZeros().toPlainString() : "" : "");
                        if (contractPayment.getPaymentMoney() == null && contractPayment.getContractTotal() == null) {
                            cell15.setCellValue("");
                        } else {
                            cell15.setCellValue(BigDecimalUtils.stringToDate(contractPayment.getPaymentMoney() != null ? contractPayment.getPaymentMoney() : new BigDecimal(0), contractPayment.getContractTotal() != null ? contractPayment.getContractTotal() : new BigDecimal(0)));
                        }

                        //三级目录
                        cell16.setCellValue(paymentInfo.getPaymentNumber() != null ? paymentInfo.getPaymentNumber() : "");
                        cell17.setCellValue(paymentInfo.getPaymentMoney() != null ? paymentInfo.getPaymentMoney().stripTrailingZeros().toPlainString() != null ? paymentInfo.getPaymentMoney().stripTrailingZeros().toPlainString() : "" : "");
                        cell18.setCellValue(paymentInfo.getPaymentTime() != null ? sdf.format(paymentInfo.getPaymentTime()) : "");
                        if (contractPayment.getContractTotal() == null && paymentInfo.getPaymentMoney() == null) {
                            cell19.setCellValue("");
                        } else {
                            cell19.setCellValue(BigDecimalUtils.stringToDate(paymentInfo.getPaymentMoney() != null ? paymentInfo.getPaymentMoney() : new BigDecimal(0), contractPayment.getContractTotal() != null ? contractPayment.getContractTotal() : new BigDecimal(0)));
                        }
                        //设置单元格样式;
                        CellStyle contentStyle = createContentStyle(book);
                        CellStyle otherStyle = createOtherStyle(book);
                        //前8列背景、字体、边框、对齐方式;
                        for (int h = 0; h < 6; h++) {
                            row.getCell(h).setCellStyle(contentStyle);
                        }
                        //后4列背景、字体、边框、对齐方式、日期格式;
                        for (int s = 6; s < cols; s++) {
                            if (s == 13) {
                                row.getCell(13).setCellStyle(setDateStyle(book));//设置日期样式
                                //System.out.println(DateUtil.isCellDateFormatted(row.getCell(8)));//输出：true
                            }
                            if (s == 18) {
                                row.getCell(18).setCellStyle(setDateStyle(book));//设置日期样式
                                //System.out.println(DateUtil.isCellDateFormatted(row.getCell(8)));//输出：true
                            }
                            row.getCell(s).setCellStyle(otherStyle);
                        }
                        //单元格列宽度;
                        for (int h = 0; h < cols; h++) {
                            if (h == 2 || h == 3 || h == 5 || h == 10 || h == 11) {
                                sheet.setColumnWidth(h, 40 * 256);//设置第一类列的宽度是44个字符宽度
                            } else if (h == 10) {
                                sheet.setColumnWidth(h, 60 * 256);
                            } else {
                                sheet.setColumnWidth(h, 16 * 256);//设置第一类列的宽度是16个字符宽度,水平居中;
                                CellStyle cellStyle = book.createCellStyle();
                                cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
                            }
                        }
                    }
                    if (contractPaymentList.size() > 0) {
                        sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.get(i).getPaymentInfoList().size() - 1, 0, 0));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() + paymentInfoList.size() - 1, 1, 1));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() + paymentInfoList.size() - 1, 2, 2));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() + paymentInfoList.size() - 1, 3, 3));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() + paymentInfoList.size() - 1, 4, 4));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() + paymentInfoList.size() - 1, 5, 5));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + contractPaymentList.size() + paymentInfoList.size() - 1, 6, 6));

                        start += contractPaymentList.size();
                    }

                    //前5列根据每一项重点工作的任务及目标分解数量来决定跨行数;
                    if (paymentInfoList.size() > 0) {
                        sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 7, 7));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 8, 8));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 9, 9));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 10, 10));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 11, 11));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 12, 12));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 13, 13));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 14, 14));
                        sheet.addMergedRegion(new CellRangeAddress(start, start + paymentInfoList.size() - 1, 15, 15));
                        start += paymentInfoList.size();
                    }
                }


            }

            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.addHeader("Content-disposition", "attachment;filename=" + "合计及支付详情");
            // 写入数据 把相应的Excel 工作簿存盘
            OutputStream os = response.getOutputStream();
            book.write(os);
            os.flush();
            os.close();
        }
		/*}catch (Exception e){
			e.getMessage();
		}*/
    }


    /**
     * 给excel设置标题
     *
     * @param sheet
     */
    public void createTitle(Workbook book, Sheet sheet, String headTitle) {
        CellStyle style = createTitleStyle(book);
        Row row = sheet.createRow(0);// 创建第一行,设置表的标题;
        row.setHeightInPoints(36);//设置行的高度是34个点
        Cell cell = row.createCell(0);
        cell.setCellValue(headTitle);
        cell.setCellStyle(style);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cols - 1));//第一行跨表所有列;
    }


    /**
     * 设置导出表的表头
     *
     * @param book
     * @param sheet
     */
    private void createHeads(Workbook book, Sheet sheet) {
        // 设置单元格格式(文本)
        // 第二行为表头行
        String title = "";
        CellStyle style = createHeadStyle(book);

        Row row = sheet.createRow(1);// 创建第一行
        row.setHeightInPoints(22);//设置行的高度是20个点
        for (int j = 0; j < 7; j++) {
            Cell cell = row.createCell(j);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            //二级目录
            if (j == 0) {
                title = "合同名称";
            }
            if (j == 1) {
                title = "报装单位";
            }
            if (j == 2) {
                title = "合同金额";
            }
            if (j == 3) {
                title = "决算金额";
            }
            if (j == 4) {
                title = "招标批次";
            }
            if (j == 5) {
                title = "收款金额";
            }
            if (j == 6) {
                title = "收款占比";
            }
            cell.setCellValue(title);
            cell.setCellStyle(style);
        }
    }


    /**
     * 设置导出表的表头
     *
     * @param book
     * @param sheet
     */
    private void createHead(Workbook book, Sheet sheet) {
        // 设置单元格格式(文本)
        // 第二行为表头行
        String title = "";
        CellStyle style = createHeadStyle(book);

        Row row = sheet.createRow(4);// 创建第一行
        row.setHeightInPoints(22);//设置行的高度是20个点
        for (int j = 0; j < cols; j++) {
            Cell cell = row.createCell(j);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            //二级目录
            if (j == 0) {
                title = "合同编号";
            }
            if (j == 1) {
                title = "合同名称";
            }
            if (j == 2) {
                title = "施工单位";
            }
            if (j == 3) {
                title = "合同金额";
            }
            if (j == 4) {
                title = "决算金额";
            }
            if (j == 5) {
                title = "招标批次";
            }
            if (j == 6) {
                title = "合同签订时间";
                sheet.setColumnWidth(j, title.getBytes().length * 2 * 256);
            }
            if (j == 7) {
                title = "已付款金额";
            }
            if (j == 8) {
                title = "付款比例";
            }

            //第三级目录
            /*if (j == 9) {
                title = "支付次数";
            }*/
            if (j == 9) {
                title = "支付金额";
            }
            if (j == 10) {
                title = "支付时间";
                sheet.setColumnWidth(j, title.getBytes().length * 2 * 256);
            }
            if (j == 11) {
                title = "本次支付占比";
            }
            cell.setCellValue(title);
            cell.setCellStyle(style);
        }
    }


    /**
     * 创建标题样式
     *
     * @param book
     * @return
     */
    public CellStyle createTitleStyle(Workbook book) {
        CellStyle cellStyle = book.createCellStyle();
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        Font font = book.createFont();
        font.setFontHeightInPoints((short) 20); // 字体大小
        font.setFontName("宋体");
        font.setBold(true); // 粗体
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 创建表头样式
     *
     * @param book
     * @return
     */
    public CellStyle createHeadStyle(Workbook book) {
        CellStyle cellStyle = book.createCellStyle();
        /*cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框*/
        //cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 填充单元格
        // cellStyle.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index); // 填浅蓝色
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        Font font = book.createFont();
        font.setFontHeightInPoints((short) 11); // 字体大小
        font.setFontName("黑体");
        font.setBold(true); // 粗体
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 创建内容部分前8列单元格样式
     *
     * @param book
     * @return
     */
    public CellStyle createContentStyle(Workbook book) {
        CellStyle cellStyle = book.createCellStyle();
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 填充单元格
        cellStyle.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index); // 填浅蓝色
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setWrapText(true);//自动换行
        Font font = book.createFont();
        font.setFontHeightInPoints((short) 11); // 字体大小
        font.setFontName("宋体");
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 创建内容其它部分单元格样式
     *
     * @param book
     * @return
     */
    public CellStyle createOtherStyle(Workbook book) {
        CellStyle cellStyle = book.createCellStyle();
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setWrapText(true);//自动换行
        Font font = book.createFont();
        font.setFontHeightInPoints((short) 11); // 字体大小
        font.setFontName("宋体");
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 设置字符串内注解样式;
     *
     * @param book
     * @param str  传入的待处理字符串;
     * @return
     */
    public RichTextString noteFontStyle(Workbook book, String str) {
        //定义字体
        Font hFont = book.createFont();
        hFont.setFontHeightInPoints((short) 10);//字体大小
        hFont.setFontName("楷体");
        RichTextString richString = null;
        try {//2007版excel
            richString = new XSSFRichTextString(str);//2007版excel
        } catch (Exception e) {
            richString = new HSSFRichTextString(str);//2003版excel
        }
        if (str.indexOf("（") == -1) {
            return richString;
        }
        //字体样式设置到字符串中;
        richString.applyFont(str.indexOf("（"), str.indexOf("）"), hFont);
        return richString;
    }

    /**
     * 设置日期格式;
     *
     * @return
     */
    public CellStyle setDateStyle(Workbook book) {
        CellStyle style = book.createCellStyle();
        try {//2007版excel
            DataFormat format = book.createDataFormat();
            style.setDataFormat(format.getFormat("yyyy年MM月dd日"));
        } catch (Exception e) {//2003版excel
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy年MM月dd日"));
        }

        return style;
    }

}
